﻿IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'ReporteCostos')
	BEGIN
		DROP  Procedure  ReporteCostos
	END

GO

CREATE Procedure ReporteCostos

	(
		@ProductoID int
	)

AS

SELECT     
dbo.Productos.productoID AS ID, 
dbo.Productos.nombre AS Producto, 
dbo.Productos.precio AS Precio, 
dbo.Insumos.nombre AS Insumo, 
dbo.Productos_Insumos.cantidad AS Cantidad, 
dbo.Insumos.costo AS CostoUnitario, 
dbo.Productos_Insumos.cantidad * dbo.Insumos.costo AS Costo
FROM dbo.Productos 
INNER JOIN dbo.Productos_Insumos ON dbo.Productos.productoID = dbo.Productos_Insumos.productoID 
INNER JOIN dbo.Insumos ON dbo.Productos_Insumos.insumoID = dbo.Insumos.insumoID
WHERE 
CASE 
	WHEN @ProductoID IS NULL THEN 1
	WHEN @ProductoID = dbo.Productos.productoID THEN 1
	ELSE 0
END = 1

GO


GRANT EXEC ON ReporteCostos TO PUBLIC

GO

